<?php

namespace App\Http\Controllers\TaskSalary;

use App\Http\Controllers\Controller;
use App\Models\Personnel\PersonnelCleaner;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use App\Models\Bus\Bus;
use App\Models\Bus\BusIsCheck;

class EleTaskSalaryController extends Controller
{
    /**
     * 生成任务工资
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function eleTaskDalary(Request $request)
    {
        $params = $request->all();
        //不考核车辆 非电动车
        $noTypeBus = DB::table('akbus_bus')
            ->where('type', 1)
            ->pluck('license_plate_num')
            ->toArray();

        foreach ($noTypeBus as $k => $v) {
            $noTypeBus[$k] = str_replace('陕G', '', $noTypeBus[$k]);
        }
        if (isset($params['date'])) {
            $time = strtotime($params['date']);
            $start_date = date('Y-m-26', strtotime(date('Y', $time).'-'.(date('m', $time) - 1).'-26'));
            $end_date = date('Y-m-25', strtotime(date('Y', $time).'-'.(date('m', $time)).'-25'));
            $date = date('Y-m-01', strtotime($params['date']));

            $sk_start_date = date('Y-m-26 00:00:00', strtotime(date('Y', $time).'-'.(date('m', $time) - 1).'-26'));
            $sk_end_date = date('Y-m-25 23:59:59', strtotime(date('Y', $time).'-'.(date('m', $time)).'-25'));
        }

        //不考核车辆 非电动车
        $noCheckBus = DB::table('akbus_bus as a')
            ->join('akbus_bus_is_check as b', 'a.id', '=', 'b.bus_id')
            ->where('b.is_check', 1)
            ->where('b.month', date('Y-m-d', strtotime($params['date'])))
            ->where('a.type', 1)
            ->pluck('a.license_plate_num')
            ->toArray();

        $incomeLineData = DB::table('v_income_pay')
            ->where('日期', '>=', $start_date)
            ->where('日期', '<=', $end_date)
            ->whereIn('车牌号', $noTypeBus)
            ->get()
            ->groupBy('线路')
            ->all();
        $incomeLineData_check = DB::table('v_income_pay')
            ->where('日期', '>=', $start_date)
            ->where('日期', '<=', $end_date)
            ->whereIn('车牌号', $noCheckBus)
            ->get()
            ->groupBy('线路')
            ->all();
        //充电
        $eleData = DB::table('akbus_charge as c')
            ->leftJoin('akbus_bus_distribution as d', 'c.license_plate_num', '=', 'd.license_plate_num')
            ->select(
                'c.license_plate_num',
                'd.line',
                DB::raw("SUM(c.trading_power) as total_trading_power"),
                DB::raw("SUM(c.act_trading_amt) as total_trading_amt")
            )
            ->where('c.charging_end_at', '>=', $sk_start_date)
            ->where('c.charging_end_at', '<=', $sk_end_date)
            ->whereIn('c.license_plate_num', $noTypeBus)
            ->groupBy('c.license_plate_num')
            ->get()
            ->toArray();
        //充电   纳入考核车辆
        $eleCheckData = DB::table('akbus_charge as c')
            ->leftJoin('akbus_bus_distribution as d', 'c.license_plate_num', '=', 'd.license_plate_num')
            ->select(
                'c.license_plate_num',
                'd.line',
                DB::raw('SUM(c.trading_power) as total_trading_power'),
                DB::raw('SUM(c.act_trading_amt) as total_trading_amt')
            )
            ->where('c.charging_end_at', '>=', $sk_start_date)
            ->where('c.charging_end_at', '<=', $sk_end_date)
            ->whereIn('c.license_plate_num', $noCheckBus)
            ->groupBy('c.license_plate_num')
            ->get()
            ->toArray();
        //月总一卡通免费次数
        $cardData = DB::table('akbus_income_card')
            ->select('license_plate_num', DB::raw('SUM(free_count) as total_free_count'))
            ->where('date', '>=', $start_date)
            ->where('date', '<=', $end_date)
            ->whereIn('license_plate_num', $noTypeBus)
            ->groupBy('license_plate_num')
            ->get()
            ->toArray();
        $cardCheckData = DB::table('akbus_income_card')
            ->select('license_plate_num', 'line', DB::raw('SUM(free_count) as total_free_count'))
            ->where('date', '>=', $start_date)
            ->where('date', '<=', $end_date)
            ->whereIn('license_plate_num', $noCheckBus)
            ->groupBy('line')
            ->get()
            ->toArray();
        //月总邮储公益运量
        $postData = DB::table('akbus_income_post')->select(
            'license_plate_num',
            DB::raw('SUM(welfare_count) as total_free_count')
        )
            ->where('date', '>=', $start_date)
            ->where('date', '<=', $end_date)
            ->whereIn('license_plate_num', $noTypeBus)
            ->groupBy('license_plate_num')
            ->get()
            ->toArray();
        //月总公里数(一条线路停运，6辆车)
        $mileageData = DB::table('akbus_mileage')->select('license_plate_num', 'line',
            DB::raw('SUM(gps_mileage) as total_km'))
            ->where('date', '>=', $start_date)
            ->where('date', '<=', $end_date)
            ->whereIn('license_plate_num', $noTypeBus)
            ->groupBy('license_plate_num')
            ->get()
            ->toArray();
        $mileageData_check = DB::table('akbus_mileage')->select('license_plate_num', 'line',
            DB::raw('SUM(gps_mileage) as total_km'))
            ->where('date', '>=', $start_date)
            ->where('date', '<=', $end_date)
            ->whereIn('license_plate_num', $noCheckBus)
            ->groupBy('license_plate_num')
            ->get()
            ->toArray();
        //每条线路   车辆数
        $countLineBus = DB::table('akbus_bus_distribution')
            ->select('line', DB::raw('count(license_plate_num) as count_line_bus'))
            ->whereIn('license_plate_num', $noTypeBus)
            ->groupBy('line')->get()->toArray();

        $lineData = [];
        foreach ($incomeLineData as $line => $v) {
            $busData = $v->groupBy('车牌号')->all();
            foreach ($busData as $bus => $vv) {
                $lineData[$line][$bus] = [
                    '车牌号'                 => $bus,
                    '线路'                  => $line,
                    'total_chartered_bus' => $vv->sum('包车收入'),
                    'total_IPass'         => $vv->sum('一卡通收入'),
                    'total_post'          => $vv->sum('邮储收入'),
                    'total_cash'          => $vv->sum('现金收入'),
                ];
            }
        }
        $lineCheckData = [];
        foreach ($incomeLineData_check as $line => $v) {
            $busData = $v->groupBy('车牌号')->all();
            foreach ($busData as $bus => $vv) {
                $lineCheckData[$line][$bus] = [
                    '车牌号'                 => $bus,
                    '线路'                  => $line,
                    'total_chartered_bus' => $vv->sum('包车收入'),
                    'total_IPass'         => $vv->sum('一卡通收入'),
                    'total_post'          => $vv->sum('邮储收入'),
                    'total_cash'          => $vv->sum('现金收入'),
                ];
            }
        }
        $data['incomeLineData'] = $lineData;
        $data['cardCheckData'] = $cardCheckData;
        $data['incomeLineData_check'] = $lineCheckData;
        $data['cardData'] = $cardData;
        $data['postData'] = $postData;
        $data['mileageData'] = $mileageData;
        $data['mileageData_check'] = $mileageData_check;
        $data['countLineBus'] = $countLineBus;
        $data['eleData'] = $eleData;
        $data['eleCheckData'] = $eleCheckData;
        $result = $this->transData($data, $date);

        return response()->formatJson(['result' => $result]);
    }

    public function transData($data, $date)
    {
        $busIsCheck = BusIsCheck::query()->with('bus:id,license_plate_num')->where('month', $date)->get()->toArray();

        foreach ($busIsCheck as $key => $value) {
            $busIsCheck[$key]['license_plate_num'] = str_replace('陕G', '', $value['bus']['license_plate_num']);
            unset($busIsCheck[$key]['bus']);
        }
        $result = [];
        $busIsCheck = collect($busIsCheck);
        foreach ($data['incomeLineData'] as $key_line => $v_line) {
            $datainfo = [];
            $incomeData = $v_line;
            foreach ($incomeData as $key => $v) {
                $isCheckBus = $busIsCheck->where('license_plate_num', $key)->first();
                $datainfo[$key]['is_check'] = $isCheckBus?$isCheckBus['is_check']:0;
                $datainfo[$key]['line'] = $key_line == 0 ? '停运' : $key_line;
                $datainfo[$key]['license_plate_num'] = $key;
                $datainfo[$key]['monthly_income'] = $v['total_IPass'] + $v['total_post'] + $v['total_cash']; //月收入
                $datainfo[$key]['contract'] = $v['total_chartered_bus']; //包车收入
                $total_card_free = 0;
                foreach ($data['cardData'] as $c_v) {
                    if ($key == $c_v['license_plate_num']) {
                        $total_card_free = $c_v['total_free_count'];
                    }
                }
                // $total_post_good = 0;
                // foreach ($data['postData'] as $p_v) {
                //     if ($key == $p_v['license_plate_num']) {
                //         $total_post_good = $p_v['total_free_count'];
                //     }
                // }
                // 修改 免票收入不加邮储+$total_post_good
                $datainfo[$key]['free'] = $total_card_free;//免票运量
                $datainfo[$key]['free_income'] = $datainfo[$key]['free'] * 1.2;//免票收入
                $datainfo[$key]['income_total'] = $datainfo[$key]['monthly_income'] + $datainfo[$key]['contract'] +
                    $datainfo[$key]['free_income'];//收入合计
                $month_km = 0;
                foreach ($data['mileageData'] as $m_v) {
                    if ($key == $m_v['license_plate_num']) {
                        $month_km = $m_v['total_km'];
                    }
                }
                $datainfo[$key]['monthly_km'] = $month_km;//月公里数
                $month_km_check = 0;//纳入考核
                foreach ($data['mileageData_check'] as $m_v) {
                    if ($key_line == $m_v['line']) {
                        $month_km_check += $m_v['total_km'];
                    }
                }
                $total_trading_power = 0;
                $total_trading_amt = 0;
                $total_line_trading_power = 0;
                foreach ($data['eleData'] as $s_v) {
                    if ($key == $s_v['license_plate_num']) {
                        $total_trading_power = $s_v['total_trading_power'];
                        $total_trading_amt = $s_v['total_trading_amt'];
                    }
                    if ($key_line == $s_v['line']) {
                        $total_line_trading_power += $s_v['total_trading_power'];
                    }
                }
                $datainfo[$key]['m_ele_total'] = $total_trading_power;//月充电
                $datainfo[$key]['monthly_cost'] = $total_trading_amt;//月充电(金额)
                $total_line_trading_power_check = 0;
                foreach ($data['eleCheckData'] as $s_c_v) {
                    if ($key_line == $s_c_v['line']) {
                        $total_line_trading_power_check += $s_c_v['total_trading_power'];
                    }
                }
                $total_bus=0;
                if(count($data['incomeLineData_check'])>0&&isset($data['incomeLineData_check'][$key_line])){
                    $total_bus = $key_line == 0 ? 0 :count($data['incomeLineData_check'][$key_line]);
                }
                if ($total_bus == 0) {
                    $air_aver = 0;
                    $datainfo[$key]['ele_rate'] = 0;
                    $datainfo[$key]['ele_increment'] = (float) $datainfo[$key]['m_ele_total'];
                } else {
                    $air_aver = round($total_line_trading_power_check / $total_bus, 4);
                    $datainfo[$key]['ele_increment'] = (float) $datainfo[$key]['m_ele_total'] - (float) $air_aver;//加气量 增加额
                    if ($datainfo[$key]['ele_increment'] == 0 || $air_aver == 0) {
                        $datainfo[$key]['ele_rate'] = 0;
                    } else {
                        if ($air_aver == 0) {
                            $datainfo[$key]['ele_rate'] = 0;
                        } else {
                            $datainfo[$key]['ele_rate'] = round($datainfo[$key]['ele_increment'] / (float) $air_aver,
                                4);//加气量 增加率
                        }
                    }
                }

                $total_card_free_check = 0;
                foreach ($data['cardCheckData'] as $c_v) {
                    if ($key_line == $c_v['line']) {
                        $total_card_free_check = $c_v['total_free_count'];
                    }
                }
                // 修改 免票收入不加邮储+$total_post_good
                $free_income_check = $total_card_free_check * 1.2; //免票收入
                $line_income_total = 0;
                if (count($data['incomeLineData_check']) > 0&&isset($data['incomeLineData_check'][$key_line])) {
                    $line_income_total =
                        array_sum(array_column($data['incomeLineData_check'][$key_line], 'total_IPass')) +
                        array_sum(array_column($data['incomeLineData_check'][$key_line], 'total_post')) +
                        array_sum(array_column($data['incomeLineData_check'][$key_line], 'total_cash')) +
                        array_sum(array_column($data['incomeLineData_check'][$key_line], 'total_chartered_bus')) +
                        $free_income_check;
                } else {
                    $line_income_total = 0;
                }
                if ($total_bus == 0) {
                    $income_aver = 0;
                } else {
                    $income_aver = round($line_income_total / $total_bus, 4);
                }
                $datainfo[$key]['income_increment'] = (float) $datainfo[$key]['income_total'] -
                    (float) $income_aver;//收入 增加额
                if ($datainfo[$key]['income_increment'] == 0 || $income_aver == 0) {
                    $datainfo[$key]['income_rate'] = 0;
                } else {
                    if ($income_aver == 0) {
                        $datainfo[$key]['income_rate'] = 0;
                    } else {
                        $datainfo[$key]['income_rate'] = round(
                            $datainfo[$key]['income_increment'] / (float) $income_aver,
                            4
                        );//收入 增加率
                    }
                }
                if ($total_bus == 0) {
                    $chartered_aver = 0;
                } else {
                    $chartered_aver = round($month_km_check / $total_bus, 4);
                    // $chartered_aver = round($line_chartered_total / $total_bus, 4);
                }
                $datainfo[$key]['mileage_increment'] = (float) $datainfo[$key]['monthly_km'] -
                    (float) $chartered_aver;//公里数 增加额
                if ($datainfo[$key]['mileage_increment'] == 0 || $chartered_aver == 0) {
                    $datainfo[$key]['mileage_rate'] = 0;
                } else {
                    if ($chartered_aver == 0) {
                        $datainfo[$key]['mileage_rate'] = 0;
                    } else {
                        $datainfo[$key]['mileage_rate'] = round(
                            $datainfo[$key]['mileage_increment'] / (float) $chartered_aver,
                            4
                        );//公里数 增加率
                    }
                }
                $datainfo[$key]['ele_grade_one'] = 40;
                $datainfo[$key]['income_grade_one'] = 40;
                $datainfo[$key]['traffic_grade_two'] = 15;
                $datainfo[$key]['mileage_grade_one'] = 5;
                $datainfo[$key]['date'] = $date;
                $taskSalary = DB::table('akbus_task_salary_electric')->where('date', $date)
                    ->where('line', $key_line)
                    ->where('license_plate_num', $key)
                    ->first();
                if (isset($taskSalary['id'])) {
                    unset($datainfo[$key]['date'], $datainfo[$key]['license_plate_num']);
                    DB::table('akbus_task_salary_electric')->where('id', $taskSalary['id'])->update($datainfo[$key]);
                } else {
                    DB::table('akbus_task_salary_electric')->insertGetId($datainfo[$key]);
                }
            }
            $result[$key_line] = $datainfo;
        }
        return $result;
    }

    /**
     * 生成任务工资列表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function eleTaskDalaryList(Request $request)
    {
        $params = $countParams = $request->all();
        //每条线路   车辆数
        $line = DB::table('akbus_bus_distribution')
            ->groupBy('line')->get()->toArray();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $noCheckBus = BusIsCheck::query()
            ->with('bus:id,license_plate_num')
            ->whereHas('bus', function ($query) {
                $query->where('type', 1);
            })
            ->where('month', date('Y-m-01', strtotime($params['date'])))
            ->get()
            ->toArray();
        if (isset($params['date'])) {
            $date = date('Y-m-01', strtotime($params['date']));
        } else {
            $date = date('Y-m-01');
        }
        $result = $this->getTaskDalaryList($params)->get()->toArray();
        unset($params['line']);
        $result_total = $this->getTaskDalaryList($params)->get()->toArray();
        if ($result) {
            $result = $this->transTaskDalaryListData($result, $noCheckBus);
            $total_data = $this->transTaskDalaryTotalData($result_total);
            $result[] = $total_data;
        }
        foreach ($result as $key => $bus) {
            foreach ($bus as $value) {
                $result[$key]['edit'] = false;
            }
        }
        $noCheckBusCollect = collect(array_column($noCheckBus, 'bus'));
        foreach ($result as $key => $val) {
            if ($val['id'] !== null) {
                $isCheckItem = $noCheckBusCollect->where('license_plate_num', '陕G'.$val['license_plate_num'])->all();
                foreach ($isCheckItem as $k => $v) {
                    $result[$key]['is_check'] = $noCheckBus[$k]['is_check'];
                }
                $result[$key]['line'] = $val['line'] == 0 ? '停运' : $val['line'].'路';
                //            $result[$key]['license_plate_num'] = '陕G'.$val['license_plate_num'];
                $result[$key]['ele_rate'] = ($val['ele_rate'] * 100).'%';
                $result[$key]['income_rate'] = ($val['income_rate'] * 100).'%';
                $result[$key]['mileage_rate'] = ($val['mileage_rate'] * 100).'%';
            }
        }
        return response()->formatJson(['result' => $result, 'line' => $line, 'date' => $date]);
    }

    public function getTaskDalaryList($params)
    {
        $query = DB::table('akbus_task_salary_electric');
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
            if ($params['date'] == '1970-01-01') {
                $params['date'] = date('Y-m-01');
            }
        } else {
            $params['date'] = date('Y-m-01');
        }
        $query->where('date', '=', $params['date']);
        if (isset($params['license_plate_num'])) {
            $query->where('license_plate_num', 'like', '%'.$params['license_plate_num'].'%');
        }
        if (isset($params['line']) && $params['line'] !== -1) {
            $query->where('line', '=', $params['line']);
        }
        // if (isset($params['pageNumber']) && isset($params['pageSize'])) {
        //     $query
        //         ->limit($params['pageSize'])
        //         ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        // }
        $query->orderBy('line', 'asc');
        if (isset($params['sort'])) {
            if ($params['sort_order'] != "normal" && $params['sort_order'] != null) {
                $query->orderBy($params['sort'], $params['sort_order']);
            } else {
                if ($params['sort'] != null) {
                    $query->orderBy($params['sort'], 'asc');
                } else {
                    $query->orderBy('license_plate_num', 'asc');
                }
            }
        } else {
            // $query->orderBy('ranking', 'asc');
            $query->orderBy('license_plate_num', 'asc');
        }
        return $query;
    }

    public function transTaskDalaryListData($data, $noCheckBus)
    {
        foreach ($data as $key => $bus) {
            foreach ($bus as $value) {
                $data[$key]['edit'] = false;
            }
        }
        $dataNew = [];
        $lines = collect($data)->groupBy('line')->toArray();
        foreach ($lines as $lineKey => $v) {
            $line_monthly_income = 0;
            $line_contract = 0;
            $line_free = 0;
            $line_free_income = 0;
            $line_income_total = 0;
            $line_monthly_km = 0;
            $line_monthly_cost = 0;
            $line_m_ele_total = 0;
            $line_ele_increment = 0;
            $line_ele_rate = 0;
            $line_income_increment = 0;
            $line_income_rate = 0;
            $line_mileage_increment = 0;
            $line_mileage_rate = 0;
            $line_reward_punish = 0;

            // 获取线路数据
            $lineData = collect($data)->where('line', $lineKey)->all();

            $dataNew = array_merge($dataNew, $lineData);
            $line_is_check = 0;
            foreach ($lineData as $key => $line) {
                foreach ($noCheckBus as $nk => $nv) {
                    if ('陕G'.$line['license_plate_num'] == $nv['bus']['license_plate_num']) {
                        if ($nv['is_check'] == 1) {
                            $line_monthly_income += $line['monthly_income'];
                            $line_contract += $line['contract'];
                            $line_free += $line['free'];
                            $line_free_income += $line['free_income'];
                            $line_income_total += $line['income_total'];
                            $line_monthly_km += $line['monthly_km'];
                            $line_monthly_cost += $line['monthly_cost'];
                            $line_m_ele_total += $line['m_ele_total'];
                            $line_ele_increment += $line['ele_increment'];
                            $line_ele_rate += $line['ele_rate'];
                            $line_income_increment += $line['income_increment'];
                            $line_income_rate += $line['income_rate'];
                            $line_mileage_increment += $line['mileage_increment'];
                            $line_mileage_rate += $line['mileage_rate'];
                            $line_reward_punish += $line['reward_punish'];
                            $line_is_check++;
                        }
                    }
                }
            }
            $dataNew[] = [
                'id'                => null,
                'line'              => '线路合计',
                'license_plate_num' => '-',
                'monthly_income'    => number_format($line_monthly_income, 2),
                'contract'          => number_format($line_contract, 2),
                'free'              => number_format($line_free, 2),
                'free_income'       => number_format($line_free_income, 2),
                'income_total'      => number_format($line_income_total, 2),
                'monthly_km'        => number_format($line_monthly_km, 2),
                'monthly_cost'      => number_format($line_monthly_cost, 2),
                'm_ele_total'       => number_format($line_m_ele_total, 2),
                'ele_increment'     => '',
                'ele_rate'          => '',
                'income_increment'  => '',
                'line_income_rate'  => '',
                'mileage_increment' => '',
                'mileage_rate'      => '',
                'reward_punish'     => number_format($line_reward_punish, 2),
            ];
            $dataNew[] = [
                'id'                => null,
                'line'              => '线路均值',
                'license_plate_num' => '-',
                'monthly_income'    => $line_is_check == 0 ? 0 : number_format($line_monthly_income / $line_is_check,
                    2),
                'contract'          => $line_is_check == 0 ? 0 : number_format($line_contract / $line_is_check, 2),
                'free'              => $line_is_check == 0 ? 0 : number_format($line_free / $line_is_check, 2),
                'free_income'       => $line_is_check == 0 ? 0 : number_format($line_free_income / $line_is_check, 2),
                'income_total'      => $line_is_check == 0 ? 0 : number_format($line_income_total / $line_is_check, 2),
                'monthly_km'        => $line_is_check == 0 ? 0 : number_format($line_monthly_km / $line_is_check, 2),
                'monthly_cost'      => $line_is_check == 0 ? 0 : number_format($line_monthly_cost / $line_is_check, 2),
                'm_ele_total'       => $line_is_check == 0 ? 0 : number_format($line_m_ele_total / $line_is_check, 2),
                'ele_increment'     => '',
                'ele_rate'          => '',
                'income_increment'  => '',
                'line_income_rate'  => '',
                'mileage_increment' => '',
                'mileage_rate'      => '',
                'reward_punish'     => $line_is_check == 0 ? 0 : number_format($line_reward_punish / $line_is_check, 2),
            ];
        }

        return $dataNew;
    }

    /**
     * 总合计
     */
    public function transTaskDalaryTotalData($data)
    {
        foreach ($data as $key => $bus) {
            foreach ($bus as $value) {
                $data[$key]['edit'] = false;
            }
        }
        $dataNew = [];
        $monthly_income = 0;
        $contract = 0;
        $free = 0;
        $free_income = 0;
        $income_total = 0;
        $monthly_km = 0;
        $monthly_cost = 0;
        $m_ele_total = 0;
        $ele_increment = 0;
        $ele_rate = 0;
        $income_increment = 0;
        $income_rate = 0;
        $mileage_increment = 0;
        $mileage_rate = 0;
        $reward_punish = 0;
        $lines = collect($data)->groupBy('line')->toArray();
        foreach ($lines as $lineKey => $v) {
            $line_monthly_income_t = 0;
            $line_contract_t = 0;
            $line_free_t = 0;
            $line_free_income_t = 0;
            $line_income_total_t = 0;
            $line_monthly_km_t = 0;
            $line_monthly_cost_t = 0;
            $line_m_ele_total_t = 0;
            $line_ele_increment_t = 0;
            $line_ele_rate_t = 0;
            $line_income_increment_t = 0;
            $line_income_rate_t = 0;
            $line_mileage_increment_t = 0;
            $line_mileage_rate_t = 0;
            $line_reward_punish_t = 0;

            // 获取线路数据
            $lineData = collect($data)->where('line', $lineKey)->all();
            foreach ($lineData as $key => $line) {
                $line_monthly_income_t += $line['monthly_income'];
                $line_contract_t += $line['contract'];
                $line_free_t += $line['free'];
                $line_free_income_t += $line['free_income'];
                $line_income_total_t += $line['income_total'];
                $line_monthly_km_t += $line['monthly_km'];
                $line_monthly_cost_t += 0;
                $line_m_ele_total_t += 0;
                $line_ele_increment_t += 0;
                $line_ele_rate_t += 0;
                $line_income_increment_t += $line['income_increment'];
                $line_income_rate_t += $line['income_rate'];
                $line_mileage_increment_t += $line['mileage_increment'];
                $line_mileage_rate_t += $line['mileage_rate'];
                $line_reward_punish_t += $line['reward_punish'];
            }
            $monthly_income += $line_monthly_income_t;
            $contract += $line_contract_t;
            $free += $line_free_t;
            $free_income += $line_free_income_t;
            $income_total += $line_income_total_t;
            $monthly_km += $line_monthly_km_t;
            $monthly_cost += $line_monthly_cost_t;
            $m_ele_total += $line_m_ele_total_t;
            $ele_increment += $line_ele_increment_t;
            $ele_rate += $line_ele_rate_t;
            $income_increment += $line_income_increment_t;
            $income_rate += $line_income_rate_t;
            $mileage_increment += $line_mileage_increment_t;
            $mileage_rate += $line_mileage_rate_t;
            $reward_punish += $line_reward_punish_t;
        }
        $dataNew = [
            'id'                => null,
            'line'              => '总合计',
            'license_plate_num' => '-',
            'monthly_income'    => number_format($monthly_income, 2),
            'contract'          => number_format($contract, 2),
            'free'              => number_format($free, 2),
            'free_income'       => number_format($free_income, 2),
            'income_total'      => number_format($income_total, 2),
            'monthly_km'        => number_format($monthly_km, 2),
            'monthly_cost'      => '',
            'm_ele_total'       => '',
            'ele_increment'     => '',
            'ele_rate'          => '',
            'income_increment'  => '',
            'line_income_rate'  => '',
            'mileage_increment' => '',
            'mileage_rate'      => '',
            'reward_punish'     => number_format($reward_punish, 2),
        ];

        return $dataNew;
    }

    /**
     * 详情
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function eleTaskDalaryInfo(Request $request)
    {
        $params = $request->all();
        if (isset($params['id'])) {
            $result = DB::table('akbus_task_salary_electric')->where('id', $params['id'])->first();
            $result['ele_rate_show'] = ($result['ele_rate'] * 100).'%';
            $result['income_rate_show'] = ($result['income_rate'] * 100).'%';
            $result['mileage_rate_show'] = ($result['mileage_rate'] * 100).'%';
        } else {
            $result = [];
        }
        return response()->formatJson(['result' => $result]);
    }

    /**
     * 编辑
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function editEleTaskDalary(Request $request)
    {
        $params = $request->all();
        $id = $params['id'];
        $month = $params['date'];
        unset(
            $params['id'],
            $params['date'],
            $params['ele_rate_show'],
            $params['income_rate_show'],
            $params['mileage_rate_show']
        );
        if ($params['is_check'] == 1) {
            $paramsUpdate = [
                'ele_grade_one'       => (int) $params['ele_grade_one'],
                'ele_grade_two'       => (int) $params['ele_grade_two'],
                'ele_grade_three'     => (int) $params['ele_grade_three'],
                'income_grade_one'    => (int) $params['income_grade_one'],
                'income_grade_two'    => (int) $params['income_grade_two'],
                'income_grade_three'  => (int) $params['income_grade_three'],
                'traffic_grade_one'   => (int) $params['traffic_grade_one'],
                'traffic_grade_two'   => (int) $params['traffic_grade_two'],
                'traffic_grade_three' => (int) $params['traffic_grade_three'],
                'traffic_grade_four'  => (int) $params['traffic_grade_four'],
                'mileage_grade_one'   => (int) $params['mileage_grade_one'],
                'mileage_grade_two'   => (int) $params['mileage_grade_two'],
                'mileage_grade_three' => (int) $params['mileage_grade_three'],
            ];
            // if (
            //     $params['ele_grade_one'] != 40 || $params['ele_grade_two'] || $params['ele_grade_three'] ||
            //     $params['income_grade_one'] != 40 || $params['income_grade_two'] || $params['income_grade_three'] ||
            //     $params['traffic_grade_one'] || $params['traffic_grade_two'] != 15 || $params['traffic_grade_three'] ||
            //     $params['traffic_grade_four'] ||
            //     $params['mileage_grade_one'] != 5 || $params['mileage_grade_two'] || $params['mileage_grade_three']
            // ) {
                $paramsUpdate['total_grade'] = array_sum($paramsUpdate);
            // }
        } else {
            $paramsUpdate = [
                'ele_grade_one'       => 40,
                'ele_grade_two'       => 0,
                'ele_grade_three'     => 0,
                'income_grade_one'    => 40,
                'income_grade_two'    => 0,
                'income_grade_three'  => 0,
                'traffic_grade_one'   => 0,
                'traffic_grade_two'   => 15,
                'traffic_grade_three' => 0,
                'traffic_grade_four'  => 0,
                'mileage_grade_one'   => 5,
                'mileage_grade_two'   => 0,
                'mileage_grade_three' => 0,
            ];
            $paramsUpdate['total_grade'] = null;
        }
        $paramsUpdate['updated_at'] = date('Y-m-d H:i:s');
        $paramsUpdate['reward_punish'] = $params['reward_punish'];
        $paramsUpdate['remark'] = $params['remark'];
        $data = DB::table('akbus_task_salary_electric')->where('id', $id)->update($paramsUpdate);

        $busId = Bus::query()->where('license_plate_num', $params['license_plate_num'])->first()->id;
        $updateCheck = BusIsCheck::query()
            ->where('month', $month)
            ->where('bus_id', $busId)
            ->update(['is_check' => $params['is_check']]);

        // 重新排序
        $noCheckBus = BusIsCheck::query()
            ->with('bus:id,license_plate_num')
            ->where('is_check', 1)
            ->where('month', $month)
            ->get()
            ->toArray();
        $noCheckBus = array_column($noCheckBus, 'bus');
        $noCheckBus = array_column($noCheckBus, 'license_plate_num');

        $query = DB::table('akbus_task_salary_electric');
        $data = $query->where('date', $month)
            ->where('line', 7)
            ->orderByDesc('income_total')
            ->orderByDesc('total_grade')
            ->get()
            ->toArray();

        $lineData = collect($data)->groupBy('line')->toArray();
        foreach ($lineData as $line => $data) {
            $lineData[$line] = $this->sortArrByManyField($data, 'total_grade', SORT_DESC, 'income_total', SORT_DESC);
        }
        foreach ($lineData as $line => $data) {
            $i = 1;
            foreach ($data as $key => $v) {
                if ($v['total_grade'] > 0 && in_array('陕G'.$v['license_plate_num'], $noCheckBus, true)) {
                    DB::table('akbus_task_salary_electric')->where('id', $v['id'])->update(['ranking' => $i]);
                    $i++;
                } else {
                    DB::table('akbus_task_salary_electric')->where('id', $v['id'])->update(['ranking' => null]);
                }
            }
        }

        $result = $data ? true : false;
        $message = $result ? '修改成功' : '修改失败';

        return response()->formatJson(['result' => $result], $message);
    }

    /**
     * 编辑
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function editEleTaskDalarys(Request $request)
    {
        $params = $request->all();
        foreach ($params as $v) {
            $busId = Bus::query()->where('license_plate_num', $v['license_plate_num'])->first()->id;
            $id = $v['id'];
            $month = $v['date'];
            unset(
                $v['id'],
                $v['date'],
                $v['ele_rate_show'],
                $v['income_rate_show'],
                $v['mileage_rate_show']
            );
            if ($v['is_check'] == 1) {
                $vUpdate = [
                    'ele_grade_one'       => (int) $v['ele_grade_one'],
                    'ele_grade_two'       => (int) $v['ele_grade_two'],
                    'ele_grade_three'     => (int) $v['ele_grade_three'],
                    'income_grade_one'    => (int) $v['income_grade_one'],
                    'income_grade_two'    => (int) $v['income_grade_two'],
                    'income_grade_three'  => (int) $v['income_grade_three'],
                    'traffic_grade_one'   => (int) $v['traffic_grade_one'],
                    'traffic_grade_two'   => (int) $v['traffic_grade_two'],
                    'traffic_grade_three' => (int) $v['traffic_grade_three'],
                    'traffic_grade_four'  => (int) $v['traffic_grade_four'],
                    'mileage_grade_one'   => (int) $v['mileage_grade_one'],
                    'mileage_grade_two'   => (int) $v['mileage_grade_two'],
                    'mileage_grade_three' => (int) $v['mileage_grade_three'],
                ];
                // if (
                //     $v['ele_grade_one'] != 40 || $v['ele_grade_two'] || $v['ele_grade_three'] ||
                //     $v['income_grade_one'] != 40 || $v['income_grade_two'] || $v['income_grade_three'] ||
                //     $v['traffic_grade_one'] || $v['traffic_grade_two'] != 15 || $v['traffic_grade_three'] ||
                //     $v['traffic_grade_four'] ||
                //     $v['mileage_grade_one'] != 5 || $v['mileage_grade_two'] || $v['mileage_grade_three']
                // ) {
                $vUpdate['total_grade'] = array_sum($vUpdate);
                // }
            } else {
                $vUpdate['total_grade'] = null;
            }
            $vUpdate['updated_at'] = date('Y-m-d H:i:s');
            $vUpdate['reward_punish'] = $v['reward_punish'];
            $vUpdate['remark'] = $v['remark'];
            $vUpdate['is_check'] = $v['is_check'];
            $result = DB::table('akbus_task_salary_electric')->where('id', $id)->update($vUpdate);

            BusIsCheck::query()->where('bus_id', $busId)->where('month',
                $month)->update(['is_check' => $v['is_check']]);
            // 重新排序
            $noCheckBus = BusIsCheck::query()
                ->with('bus:id,license_plate_num')
                ->where('is_check', 1)
                ->where('month', $month)
                ->get()
                ->toArray();
            $noCheckBus = array_column($noCheckBus, 'bus');
            $noCheckBus = array_column($noCheckBus, 'license_plate_num');

            $query = DB::table('akbus_task_salary_electric');
            $data = $query->where('date', $month)
                ->where('line', 7)
                ->orderByDesc('income_total')
                ->orderByDesc('total_grade')
                ->get()
                ->toArray();

            $lineData = collect($data)->groupBy('line')->toArray();
            foreach ($lineData as $line => $data) {
                $lineData[$line] = $this->sortArrByManyField($data, 'total_grade', SORT_DESC, 'income_total',
                    SORT_DESC);
            }
            foreach ($lineData as $line => $data) {
                $i = 1;
                foreach ($data as $key => $v) {
                    if ($v['total_grade'] > 0 && in_array('陕G'.$v['license_plate_num'], $noCheckBus, true)) {
                        DB::table('akbus_task_salary_electric')->where('id', $v['id'])->update(['ranking' => $i]);
                        $i++;
                    } else {
                        DB::table('akbus_task_salary_electric')->where('id', $v['id'])->update(['ranking' => null]);
                    }
                }
            }
        }
        // $result = $data ? true : false;
        $message = $result ? '修改成功' : '修改失败';

        return response()->formatJson(['result' => $result], $message);
    }

    /**
     * 排序
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function sortEleTaskDalary(Request $request)
    {
        $params = $request->all();
        //考核车辆非充电车
        $noCheckBus = DB::table('akbus_bus')->where('is_check', 1)->where(
            'type',
            1
        )->pluck('license_plate_num')->toArray();

        $query = DB::table('akbus_task_salary_electric');
        if (isset($params['date'])) {
            $date = date('Y-m-01', strtotime($params['date']));
        } else {
            return response()->formatJson(['result' => false]);
        }
        $data = $query->where('date', '=', $date)
            ->orderByDesc('total_grade')
            ->orderByDesc('income_total')->get()->toArray();

        //每条线路   车辆数
        $line = DB::table('akbus_bus_distribution')
            ->whereIn('license_plate_num', $noCheckBus)
            ->groupBy('line')->get()->toArray();
        foreach ($line as $lv) {
            $i = 1;
            foreach ($data as $v) {
                if ($lv['line'] == (int) $v['line'] && in_array($v['license_plate_num'], $noCheckBus, true) > 0) {
                    DB::table('akbus_task_salary_electric')->where('id', $v['id'])->update(['ranking' => $i]);
                    $i++;
                }
            }
        }
        $result = true;

        return response()->formatJson(['result' => $result]);
    }
    /**********************************生成任务工资排名********************************************* */
    /**
     * 生成任务工资排名
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function eleTaskRank(Request $request)
    {
        $params = $request->all();
        //电动车
        // $noCheckBus = DB::table('akbus_bus')
        //     ->where('type', 1)
        //     ->pluck('license_plate_num')
        //     ->toArray();
        $noCheckBus = DB::table('akbus_bus as a')
                    ->join('akbus_bus_is_check as b', 'a.id', '=', 'b.bus_id')
                    ->where('b.is_check', 1)
                    ->where('b.month', date('Y-m-d', strtotime($params['date'])))
                    ->where('a.type', 1)
                    ->pluck('a.license_plate_num')
                    ->toArray();
        $query = DB::table('akbus_task_salary_electric');
        $att_query = DB::table('akbus_attendance')
            ->select(DB::raw('date(actual_sign_time) as actual_sign_date, id, driver, license_plate_num, line, actual_sign_time'))
            ->whereIn('license_plate_num', $noCheckBus);
        if (isset($params['date'])) {
            $time = strtotime($params['date']);
            $start_date = date('Y-m-26', strtotime(date('Y', $time).'-'.(date('m', $time) - 1).'-26'));
            $end_date = date('Y-m-25', strtotime(date('Y', $time).'-'.(date('m', $time)).'-25'));
            $date = date('Y-m-01', strtotime($params['date']));
            $query->where('date', '>=', $start_date);
            $query->where('date', '<=', $end_date);
            $att_query->whereBetween('actual_sign_time', [$start_date, $end_date]);
        }
        $data['task_salary'] = $query->groupBy('license_plate_num')->get()->toArray();
        // $data['attendance'] = $att_query->groupBy('driver')->get()->toArray();
        $cleaner = PersonnelCleaner::query()->get()->pluck('name')->toArray();
        $data['attendance'] = $att_query->whereNotIn('driver', $cleaner)->orderBy('line', 'asc')->get()->toArray();
        $result = $this->RankTransData2($data, $date);
        return response()->formatJson(['result' => $result]);
    }

    public function RankTransData2($datas, $date)
    {
        if (empty($datas['task_salary'])) {
            return [];
        }
        $data = collect($datas['attendance'])->groupBy('license_plate_num')->toArray();
        $postData = [];
        foreach ($data as $licensePlateNum => $item) {
            $drivers = $this->RankTransData($item, $datas['task_salary'], $date);
            foreach ($drivers as $k => $driver) {
                $postData[] = $driver;
            }
        }

        return $postData;
    }

    public function RankTransData($data, $task_salary, $date)
    {
        $datainfo = [];

        //不考核车辆 电动车
        $noCheckBus = DB::table('akbus_bus as a')
            ->join('akbus_bus_is_check as b', 'a.id', '=', 'b.bus_id')
            ->where('b.is_check', 1)
            ->where('b.month', date('Y-m-d', strtotime($date)))
            ->where('a.type', 1)
            ->pluck('a.license_plate_num')
            ->toArray();

        $attendance_data = collect($data)->groupBy('driver')->toArray();
        $start_date = date('Y-m-26',
            strtotime(date('Y', strtotime($date)).'-'.(date('m', strtotime($date)) - 1).'-26'));
        $end_date = date('Y-m-25', strtotime(date('Y', strtotime($date)).'-'.(date('m', strtotime($date))).'-25'));
        $month_day=date('t', strtotime($start_date));
        $dateList = getListFromDateRange($start_date, $end_date);
        foreach ($attendance_data as $key => $v) {
            $datainfo[$key]['line'] = $v[0]['line'];
            $datainfo[$key]['driver'] = $v[0]['driver'];
            $datainfo[$key]['date'] = $date;
            $work_day = 0;
            $dates = collect($v)->groupBy('actual_sign_date')->toArray();
            // 填充无日期
            foreach ($dateList as $d) {
                if (! array_key_exists($d, $dates)) {
                    $dates[$d] = [];
                }
            }
            ksort($dates);
            $sign = 0;
            foreach ($dates as $k => $dateData) {
                if ($dateData) {
                    $minDate = searchMin($dateData, 'actual_sign_time');
                    $maxDate = searchMax($dateData, 'actual_sign_time');
                    $workHour = number_format((strtotime($maxDate) - strtotime($minDate)) % 86400 / 3600, 1);
                    $maxHours = UTC2Normal($maxDate, 'H');
                    $normalHour = $maxHours <= 12 ? 4.5 : 6;
                    if ($workHour >= $normalHour && $workHour < 13) {
                        $sign = 0.5;
                    } elseif ($workHour >= 13) {
                        $sign = 1;
                    } else {
                        $sign = 0;
                    }
                    $work_day += $sign;
                }
            }
            $datainfo[$key]['work_day'] = $work_day;
            $v_license_plate_num = strpos($v[0]['license_plate_num'], '陕G') !== false ?
                substr($v[0]['license_plate_num'], 4) :
                $v[0]['license_plate_num'];
            foreach ($task_salary as $t_v) {
                $t_license_plate_num = strpos($t_v['license_plate_num'], '陕G') !== false ?
                    substr($t_v['license_plate_num'], 4) :
                    $t_v['license_plate_num'];
                if ($v_license_plate_num == $t_license_plate_num) {
                    $datainfo[$key]['license_plate_num'] = $v_license_plate_num;
                    if ($t_v['reward_punish'] < 0) {
                        $datainfo[$key]['reward_punish_text'] = '处罚';
                    } else {
                        $datainfo[$key]['reward_punish_text'] = '奖励';
                    }
                    $datainfo[$key]['should_reward_punish'] = $t_v['reward_punish'];
                    $datainfo[$key]['day_money'] = $t_v['reward_punish'] / (int)$month_day;
                    $datainfo[$key]['reward_punish_money'] = $work_day ? ($t_v['reward_punish'] / (int)$month_day) * $work_day : 0;
                    $datainfo[$key]['total_grade'] = $t_v['total_grade'];
                    if (in_array($t_license_plate_num, $noCheckBus, true) > 0) {
                        $datainfo[$key]['ranking'] = $t_v['ranking'];
                    }
                }
            }
            $taskSalary = DB::table('akbus_task_salary_electric_rank')
                ->where('date', $date)
                ->where('driver', $v[0]['driver'])
                ->where('license_plate_num', $v_license_plate_num)
                ->first();
            if (isset($taskSalary['id'])) {
                if ($taskSalary['real_reward_punish'] > 0) {
                    unset($datainfo[$key]['real_reward_punish'], $datainfo[$key]['reward_punish_money'], $datainfo[$key]['day_money']);
                }
                // unset($datainfo[$key]['date'], $datainfo[$key]['license_plate_num']);
                DB::table('akbus_task_salary_electric_rank')->where(
                    'id',
                    $taskSalary['id']
                )->update($datainfo[$key]);
            } else {
                DB::table('akbus_task_salary_electric_rank')->insertGetId($datainfo[$key]);
            }
        }
        return $datainfo;
    }

    public function sortArrByManyField()
    {
        $args = func_get_args();
        if (empty($args)) {
            return null;
        }
        $arr = array_shift($args);
        foreach ($args as $key => $field) {
            if (is_string($field)) {
                $temp = [];
                foreach ($arr as $index => $val) {
                    $temp[$index] = $val[$field];
                }
                $args[$key] = $temp;
            }
        }
        $args[] = &$arr;//引用值
        array_multisort(...$args);

        return array_pop($args);
    }

    /**
     * 任务工资排名列表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function eleTaskRankList(Request $request)
    {
        $params = $countParams = $request->all();
        //每条线路   车辆数
        $line = DB::table('akbus_bus_distribution')->groupBy('line')->get()->toArray();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
        } else {
            $params['date'] = date('Y-m-01');
        }
        $cleaner = PersonnelCleaner::query()->get()->pluck('name')->toArray();
        $result = $this->getTaskRankList($params)->where('should_reward_punish', '!=',0)
                                        ->whereNotIn('driver', $cleaner)->get()->toArray();
        foreach ($result as $key => $bus) {
            foreach ($bus as $value) {
                $result[$key]['edit'] = false;
            }
        }
        $bus_number_data = collect($result)->groupBy('license_plate_num')->values()->toArray();
        $data = [];
        if (count($bus_number_data) >= 12) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == 1 || $key == 2) {
                        if ($v['total_grade'] >= 100) {
                            $data[] = $v;
                        }
                    }
                    if ($key == count($bus_number_data) - 3 || $key == count($bus_number_data) - 2 || $key == count($bus_number_data) - 1) {
                        if ($v['total_grade'] <= 85 && $v['total_grade'] > 0) {
                            $data[] = $v;
                        }
                    }
                }
            }
        } elseif (count($bus_number_data) >= 6 && count($bus_number_data) < 12) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == 1 || $key == count($bus_number_data) - 2 || $key == count($bus_number_data) - 1) {
                        if ($v['total_grade'] > 0) {
                            $data[] = $v;
                        }
                    }
                }
            }
        } elseif (count($bus_number_data) >= 3 && count($bus_number_data) < 6) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == count($bus_number_data) - 1) {
                        if ($v['total_grade'] > 0) {
                            $data[] = $v;
                        }
                    }
                }
            }
        } elseif (count($bus_number_data) <= 2) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == count($bus_number_data) - 1) {
                        $data[] = $v;
                    }
                }
            }
        }

        return response()->formatJson([
            'result' => $data, 'line' => $line, 'date' => $params['date'],
        ]);
    }

    public function getTaskRankList($params)
    {
        $query = DB::table('akbus_task_salary_electric_rank');
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
            if ($params['date'] == '1970-01-01') {
                $params['date'] = date('Y-m-01');
            }
        } else {
            $params['date'] = date('Y-m-01');
        }
        $query->where('date', '=', $params['date']);
        if (isset($params['license_plate_num'])) {
            $query->where('license_plate_num', 'like', '%'.$params['license_plate_num'].'%');
        }
        // if (isset($params['line']) && $params['line'] !== "-1") {
        //     $query->where('line', '=', $params['line']);
        // }
        // if (isset($params['pageNumber']) && isset($params['pageSize'])) {
        //     $query
        //         ->limit($params['pageSize'])
        //         ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        // }

        // $query->orderBy('line', 'asc');
        $query->orderBy('ranking', 'asc');
        if (isset($params['sort'])) {
            if ($params['sort_order'] != "normal") {
                $query->orderBy($params['sort'], $params['sort_order']);
            } else {
                $query->orderBy($params['sort'], 'asc');
            }
        }
        return $query;
    }

    /**
     * 详情
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function eleTaskRankInfo(Request $request)
    {
        $params = $request->all();
        if (isset($params['id'])) {
            $result = DB::table('akbus_task_salary_electric_rank')->where('id', $params['id'])->first();
        } else {
            $result = [];
        }

        return response()->formatJson(['result' => $result]);
    }

    /**
     * 编辑多
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function editEleTaskRanks(Request $request)
    {
        $params = $request->all();
        foreach ($params as $v) {
            $id = $v['id'];
            $info = DB::table('akbus_task_salary_electric_rank')->where('id', $id)->first();
            $datainfo = [];
            if (isset($v['real_reward_punish'])) {
                if ($v['real_reward_punish'] == null || $v['real_reward_punish'] == 0) {
                    $datainfo['real_reward_punish'] = $v['real_reward_punish'];
                    $datainfo['day_money'] = $v['should_reward_punish'] / 30;
                    $datainfo['reward_punish_money'] = ($v['should_reward_punish'] / 30) * $info['work_day'];
                } else {
                    $datainfo['real_reward_punish'] = $v['real_reward_punish'];
                    $datainfo['day_money'] = $v['real_reward_punish'] / 30;
                    $datainfo['reward_punish_money'] = ($v['real_reward_punish'] / 30) * $info['work_day'];
                }
            } else {
                if ($v['should_reward_punish'] == null || $v['should_reward_punish'] == 0) {
                    $datainfo['real_reward_punish'] = $v['real_reward_punish'];
                    $datainfo['day_money'] = 0;
                    $datainfo['reward_punish_money'] = 0;
                } else {
                    $datainfo['real_reward_punish'] = $v['real_reward_punish'];
                    $datainfo['day_money'] = $v['day_money'];
                    $datainfo['reward_punish_money'] = $v['reward_punish_money'];
                }
            }
            $datainfo['remark'] = $v['remark'];
            $data = DB::table('akbus_task_salary_electric_rank')->where('id', $id)->update($datainfo);
        }
        $result = $data ? true : false;

        return response()->formatJson(['result' => $result]);
    }

    /**
     * 编辑
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function editEleTaskRank(Request $request)
    {
        $params = $request->all();
        $id = $params['id'];
        $info = DB::table('akbus_task_salary_electric_rank')->where('id', $id)->first();
        $datainfo = [];
        if (isset($params['real_reward_punish'])) {
            $datainfo['real_reward_punish'] = $params['real_reward_punish'];
            $datainfo['day_money'] = $params['real_reward_punish'] / 30;
            $datainfo['reward_punish_money'] = ($params['real_reward_punish'] / 30) * $info['work_day'];
        } else {
            $datainfo['real_reward_punish'] = $params['real_reward_punish'];
            $datainfo['day_money'] = $params['day_money'];
            $datainfo['reward_punish_money'] = $params['reward_punish_money'];
        }
        $datainfo['remark'] = $params['remark'];
        $data = DB::table('akbus_task_salary_electric_rank')->where('id', $id)->update($datainfo);
        $result = $data ? true : false;

        return response()->formatJson(['result' => $result]);
    }

    /**
     * 导出任务工资
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportEleTaskDalary(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
            'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH',
        ];
        $data = $this->getTaskDalaryList($params)->get()->toArray();

        $line = $this->getTaskDalaryList($params)->groupBy('line')->get()->toArray();

        $taskSalary = new TaskSalaryController;
        $data_total = $taskSalary->getTaskDalaryList($params)->get()->toArray();
        $line_total = collect($data_total)->groupBy('line')->toArray();
        $noCheckBus = BusIsCheck::query()
            ->with('bus:id,license_plate_num')
            ->where('month', date('Y-m-01', strtotime($params['date'])))
            ->get()
            ->toArray();
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '充电任务工资考核评分表')
            ->setCellValue('A2', '线路')->setCellValue('B2', '车牌号')
            ->setCellValue('C2', '月收入(刷卡、投币）')->setCellValue('D2', '包车收入')->setCellValue('E2', '免票运量')
            ->setCellValue('F2', '免票收入')->setCellValue('G2', '收入合计')->setCellValue('H2', '月公里数')
            ->setCellValue('I2', '月充电（金额）')->setCellValue('J2', '月充电量')->setCellValue('K2', '充电量')
            ->setCellValue('P2', '收入')->setCellValue('U2', '交通事故')
            ->setCellValue('Y2', '公里数')->setCellValue('AD2', '分数合计')->setCellValue('AE2', '排名')
            ->setCellValue('AF2', '奖惩金额')->setCellValue('AG2', '备注')->setCellValue('AH2', '是否纳入考核')
            ->setCellValue('K3', '增加额')->setCellValue('L3', '增加率')->setCellValue('M3', '增减百分比在（+－1%含）之间，奖励40分')
            ->setCellValue('N3', '节约或超过1%至3%（含）之间，奖励或扣减10分')->setCellValue('O3', '如节约或超过3%，奖励或扣减20分	')
            ->setCellValue('P3', '增加额')->setCellValue('Q3', '增加率')
            ->setCellValue('R3', '增减百分比在（+－1%含）之间，奖励40分')->setCellValue('S3', '节约或超过1%至3%（含）之间，奖励或扣减10分')
            ->setCellValue('T3', '如节约或超过3%，奖励或扣减20分')->setCellValue('U3', '交通事故（次责及以上） 单位：起')
            ->setCellValue('V3', '标准分值 15分')->setCellValue('W3', '次责及以上交通事故，每起扣减5分')
            ->setCellValue('X3', '全责致人重伤或死亡的交通事故扣减全部15分')
            ->setCellValue('Y3', '增加额')->setCellValue('Z3', '增加率')
            ->setCellValue('AA3', '增减百分比在（+－1%含）之间，奖励5分')->setCellValue('AB3', '节约或超过1%至3%（含）之间，奖励或扣减2分')
            ->setCellValue('AC3', '如节约或超过3%，奖励或扣减3分');


        $monthly_income_t = 0;
        $contract_t = 0;
        $free_t = 0;
        $free_income_t = 0;
        $income_total_t = 0;
        $monthly_km_t = 0;
        $monthly_cost_t = 0;
        $m_ele_total_t = 0;
        $ele_increment_t = 0;
        $ele_rate_t = 0;
        $income_increment_t = 0;
        $income_rate_t = 0;
        $mileage_increment_t = 0;
        $mileage_rate_t = 0;
        $reward_punish_t = 0;
        foreach ($line_total as $lineKey => $v) {
            $line_monthly_income_t = 0;
            $line_contract_t = 0;
            $line_free_t = 0;
            $line_free_income_t = 0;
            $line_income_total_t = 0;
            $line_monthly_km_t = 0;
            $line_monthly_cost_t = 0;
            $line_m_ele_total_t = 0;
            $line_ele_increment_t = 0;
            $line_ele_rate_t = 0;
            $line_income_increment_t = 0;
            $line_income_rate_t = 0;
            $line_mileage_increment_t = 0;
            $line_mileage_rate_t = 0;
            $line_reward_punish_t = 0;

            // 获取线路数据
            $lineData = collect($data)->where('line', $lineKey)->all();
            foreach ($lineData as $key => $line) {
                $line_monthly_income_t += $line['monthly_income'];
                $line_contract_t += $line['contract'];
                $line_free_t += $line['free'];
                $line_free_income_t += $line['free_income'];
                $line_income_total_t += $line['income_total'];
                $line_monthly_km_t += $line['monthly_km'];
                $line_monthly_cost_t += 0;
                $line_m_ele_total_t += 0;
                $line_ele_increment_t += 0;
                $line_ele_rate_t += 0;
                $line_income_increment_t += $line['income_increment'];
                $line_income_rate_t += $line['income_rate'];
                $line_mileage_increment_t += $line['mileage_increment'];
                $line_mileage_rate_t += $line['mileage_rate'];
                $line_reward_punish_t += $line['reward_punish'];
            }
            $monthly_income_t += $line_monthly_income_t;
            $contract_t += $line_contract_t;
            $free_t += $line_free_t;
            $free_income_t += $line_free_income_t;
            $income_total_t += $line_income_total_t;
            $monthly_km_t += $line_monthly_km_t;
            $monthly_cost_t += $line_monthly_cost_t;
            $m_ele_total_t += $line_m_ele_total_t;
            $ele_increment_t += $line_ele_increment_t;
            $ele_rate_t += $line_ele_rate_t;
            $income_increment_t += $line_income_increment_t;
            $income_rate_t += $line_income_rate_t;
            $mileage_increment_t += $line_mileage_increment_t;
            $mileage_rate_t += $line_mileage_rate_t;
            $reward_punish_t += $line_reward_punish_t;
        }
        foreach ($data as $ele_k => $ele_v) {
            if ($ele_v['id'] !== null) {
                $monthly_cost_t += $ele_v['monthly_cost'];
                $m_ele_total_t += $ele_v['m_ele_total'];
            }
        }
        $line_monthly_income = 0;
        $line_contract = 0;
        $line_free = 0;
        $line_free_income = 0;
        $line_income_total = 0;
        $line_monthly_km = 0;
        $line_monthly_cost = 0;
        $line_m_ele_total = 0;
        $line_ele_increment = 0;
        $line_ele_rate = 0;
        $line_income_increment = 0;
        $line_income_rate = 0;
        $line_mileage_increment = 0;
        $line_mileage_rate = 0;
        $line_reward_punish = 0;
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $num = 4;
        $line_i = 0;
        $line_is_check = 0;
        for ($i = 0, $iMax = count($data); $i < $iMax; $i++) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $data[$i]['line']);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $data[$i]['license_plate_num']);
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $data[$i]['monthly_income']);
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $data[$i]['contract']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $data[$i]['free']);
            $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $data[$i]['free_income']);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $data[$i]['income_total']);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $data[$i]['monthly_km']);
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num, $data[$i]['monthly_cost']);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $data[$i]['m_ele_total']);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $data[$i]['ele_increment']);
            $spreadsheet->getActiveSheet()->setCellValue('L'.$num, ($data[$i]['ele_rate'] * 100).'%');
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num, $data[$i]['ele_grade_one']);
            $spreadsheet->getActiveSheet()->setCellValue('N'.$num, $data[$i]['ele_grade_two']);
            $spreadsheet->getActiveSheet()->setCellValue('O'.$num, $data[$i]['ele_grade_three']);
            $spreadsheet->getActiveSheet()->setCellValue('P'.$num, $data[$i]['income_increment']);
            $spreadsheet->getActiveSheet()->setCellValue('Q'.$num, ($data[$i]['income_rate'] * 100).'%');
            $spreadsheet->getActiveSheet()->setCellValue('R'.$num, $data[$i]['income_grade_one']);
            $spreadsheet->getActiveSheet()->setCellValue('S'.$num, $data[$i]['income_grade_two']);
            $spreadsheet->getActiveSheet()->setCellValue('T'.$num, $data[$i]['income_grade_three']);
            $spreadsheet->getActiveSheet()->setCellValue('U'.$num, $data[$i]['traffic_grade_one']);
            $spreadsheet->getActiveSheet()->setCellValue('V'.$num, $data[$i]['traffic_grade_two']);
            $spreadsheet->getActiveSheet()->setCellValue('W'.$num, $data[$i]['traffic_grade_three']);
            $spreadsheet->getActiveSheet()->setCellValue('X'.$num, $data[$i]['traffic_grade_four']);
            $spreadsheet->getActiveSheet()->setCellValue('Y'.$num, $data[$i]['mileage_increment']);
            $spreadsheet->getActiveSheet()->setCellValue('Z'.$num, ($data[$i]['mileage_rate'] * 100).'%');
            $spreadsheet->getActiveSheet()->setCellValue('AA'.$num, $data[$i]['mileage_grade_one']);
            $spreadsheet->getActiveSheet()->setCellValue('AB'.$num, $data[$i]['mileage_grade_two']);
            $spreadsheet->getActiveSheet()->setCellValue('AC'.$num, $data[$i]['mileage_grade_three']);
            $spreadsheet->getActiveSheet()->setCellValue('AD'.$num, $data[$i]['total_grade']);
            $spreadsheet->getActiveSheet()->setCellValue('AE'.$num, $data[$i]['ranking']);
            $spreadsheet->getActiveSheet()->setCellValue('AF'.$num, $data[$i]['reward_punish']);
            $spreadsheet->getActiveSheet()->setCellValue('AG'.$num, $data[$i]['remark']);
            foreach ($noCheckBus as $nk => $nv) {
                if ('陕G'.$data[$i]['license_plate_num'] === $nv['bus']['license_plate_num']) {
                    if ($nv['is_check'] === 1) {
                        $spreadsheet->getActiveSheet()->setCellValue('AH'.$num, "是");
                        $line_monthly_income += $data[$i]['monthly_income'];
                        $line_contract += $data[$i]['contract'];
                        $line_free += $data[$i]['free'];
                        $line_free_income += $data[$i]['free_income'];
                        $line_income_total += $data[$i]['income_total'];
                        $line_monthly_km += $data[$i]['monthly_km'];
                        $line_monthly_cost += $data[$i]['monthly_cost'];
                        $line_m_ele_total += $data[$i]['m_ele_total'];
                        $line_ele_increment += $data[$i]['ele_increment'];
                        $line_ele_rate += $data[$i]['ele_rate'];
                        $line_income_increment += $data[$i]['income_increment'];
                        $line_income_rate += $data[$i]['income_rate'];
                        $line_mileage_increment += $data[$i]['mileage_increment'];
                        $line_mileage_rate += $data[$i]['mileage_rate'];
                        $line_reward_punish += $data[$i]['reward_punish'];
                        $line_is_check++;
                    } else {
                        $spreadsheet->getActiveSheet()->setCellValue('AH'.$num, "否");
                    }
                }
            }
            // 编号增加
            $num++;
        }
        $line_i++;
        $spreadsheet->getActiveSheet()->setCellValue('A'.($num + $line_i - 1), '线路合计');
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $line_i - 1), $line_monthly_income);
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $line_i - 1), $line_contract);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $line_i - 1), $line_free);
        $spreadsheet->getActiveSheet()->setCellValue('F'.($num + $line_i - 1), $line_free_income);
        $spreadsheet->getActiveSheet()->setCellValue('G'.($num + $line_i - 1), $line_income_total);
        $spreadsheet->getActiveSheet()->setCellValue('H'.($num + $line_i - 1), $line_monthly_km);
        $spreadsheet->getActiveSheet()->setCellValue('I'.($num + $line_i - 1), $line_monthly_cost);
        $spreadsheet->getActiveSheet()->setCellValue('J'.($num + $line_i - 1), $line_m_ele_total);
        $spreadsheet->getActiveSheet()->setCellValue('K'.($num + $line_i - 1), $line_ele_increment);
        $spreadsheet->getActiveSheet()->setCellValue('L'.($num + $line_i - 1), ($line_ele_rate * 100).'%');
        $spreadsheet->getActiveSheet()->setCellValue('M'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('N'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('O'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('P'.($num + $line_i - 1), $line_income_increment);
        $spreadsheet->getActiveSheet()->setCellValue('Q'.($num + $line_i - 1), ($line_income_rate * 100).'%');
        $spreadsheet->getActiveSheet()->setCellValue('R'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('S'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('T'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('U'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('V'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('W'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('X'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('Y'.($num + $line_i - 1), $line_mileage_increment);
        $spreadsheet->getActiveSheet()->setCellValue('Z'.($num + $line_i - 1), ($line_mileage_rate * 100).'%');
        $spreadsheet->getActiveSheet()->setCellValue('AA'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('AB'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('AC'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('AD'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('AE'.($num + $line_i - 1), '');
        $spreadsheet->getActiveSheet()->setCellValue('AF'.($num + $line_i - 1), $line_reward_punish);
        $spreadsheet->getActiveSheet()->setCellValue('AG'.($num + $line_i - 1), '');


        $spreadsheet->getActiveSheet()->setCellValue('A'.($num + $line_i), '线路均值');
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $line_i), '-');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_monthly_income / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_contract / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_free / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('F'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_free_income / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('G'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_income_total / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('H'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_monthly_km / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('I'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_monthly_cost / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('J'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_m_ele_total / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('K'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_ele_increment / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('L'.($num + $line_i),
            $line_is_check == 0 ? 0 : ($line_ele_rate * 100) / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('M'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('N'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('O'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('P'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_income_increment / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('Q'.($num + $line_i),
            number_format($line_is_check == 0 ? 0 : ($line_income_rate * 100) / $line_is_check, 2).'%');
        $spreadsheet->getActiveSheet()->setCellValue('R'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('S'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('T'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('U'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('V'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('W'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('X'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('Y'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_mileage_increment / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('Z'.($num + $line_i),
            number_format($line_is_check == 0 ? 0 : ($line_mileage_rate * 100) / $line_is_check, 2).'%');
        $spreadsheet->getActiveSheet()->setCellValue('AA'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AB'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AC'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AD'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AE'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AF'.($num + $line_i),
            $line_is_check == 0 ? 0 : $line_reward_punish / $line_is_check);
        $spreadsheet->getActiveSheet()->setCellValue('AG'.($num + $line_i), '');
        $spreadsheet->getActiveSheet()->getRowDimension($num + $line_i)->setRowHeight(29);

        $spreadsheet->getActiveSheet()->setCellValue('A'.($num + 1 + $line_i), '合计');
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + 1 + $line_i), $monthly_income_t);
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + 1 + $line_i), $contract_t);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + 1 + $line_i), $free_t);
        $spreadsheet->getActiveSheet()->setCellValue('F'.($num + 1 + $line_i), $free_income_t);
        $spreadsheet->getActiveSheet()->setCellValue('G'.($num + 1 + $line_i), $income_total_t);
        $spreadsheet->getActiveSheet()->setCellValue('H'.($num + 1 + $line_i), $monthly_km_t);
        $spreadsheet->getActiveSheet()->setCellValue('I'.($num + 1 + $line_i), $monthly_cost_t);
        $spreadsheet->getActiveSheet()->setCellValue('J'.($num + 1 + $line_i), $m_ele_total_t);
        $spreadsheet->getActiveSheet()->setCellValue('K'.($num + 1 + $line_i), $ele_increment_t);
        $spreadsheet->getActiveSheet()->setCellValue('L'.($num + 1 + $line_i), ($ele_rate_t * 100).'%');
        $spreadsheet->getActiveSheet()->setCellValue('M'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('N'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('O'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('P'.($num + 1 + $line_i), $income_increment_t);
        $spreadsheet->getActiveSheet()->setCellValue('Q'.($num + 1 + $line_i), ($income_rate_t * 100).'%');
        $spreadsheet->getActiveSheet()->setCellValue('R'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('S'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('T'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('U'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('V'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('W'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('X'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('Y'.($num + 1 + $line_i), $mileage_increment_t);
        $spreadsheet->getActiveSheet()->setCellValue('Z'.($num + 1 + $line_i), ($mileage_rate_t * 100).'%');
        $spreadsheet->getActiveSheet()->setCellValue('AA'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AB'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AC'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AD'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AE'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AF'.($num + 1 + $line_i), $reward_punish_t);
        $spreadsheet->getActiveSheet()->setCellValue('AG'.($num + 1 + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('AH'.($num + 1 + $line_i), '');
        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:AH1')->mergeCells('A2:A3')->mergeCells('B2:B3')
            ->mergeCells('C2:C3')->mergeCells('D2:D3')->mergeCells('E2:E3')
            ->mergeCells('F2:F3')->mergeCells('G2:G3')->mergeCells('H2:H3')->mergeCells('I2:I3')->mergeCells('J2:J3')
            ->mergeCells('AD2:AD3')->mergeCells('AE2:AE3')->mergeCells('AF2:AF3')->mergeCells('AG2:AG3')->mergeCells('AH2:AH3')
            ->mergeCells('K2:O2')->mergeCells('P2:T2')->mergeCells('U2:X2')->mergeCells('Y2:AC2');

        //  设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(8.38);
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(38);
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)
            ->getAlignment()->setWrapText(true);

        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(81);

        // $spreadsheet->getActiveSheet()->getStyle('A1:A2')->applyFromArray($borderStyleArray);
        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        for ($i = 0, $iMax = count($Letter); $i < $iMax; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);

            // $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->getFont()->setSize(15);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->getFont()->setBold(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setBold(true);
        }
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('充电车辆任务工资');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        $spreadsheet->setActiveSheetIndex(0);
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="充电车辆任务工资.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出任务工资排名
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportEleTaskRank(Request $request)
    {
        $params = $request->input();
        $Letter = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M'];
        $result = $this->getTaskRankList($params)->get()->toArray();

        $bus_number_data = collect($result)->groupBy('license_plate_num')->values()->toArray();
        $data = [];
        if (count($bus_number_data) >= 12) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == 1 || $key == 2) {
                        if ($v['total_grade'] >= 100) {
                            $data[] = $v;
                        }
                    }
                    if ($key == count($bus_number_data) - 3 || $key == count($bus_number_data) - 2 || $key == count($bus_number_data) - 1) {
                        if ($v['total_grade'] <= 85 && $v['total_grade'] > 0) {
                            $data[] = $v;
                        }
                    }
                }
            }
        } elseif (count($bus_number_data) >= 6 && count($bus_number_data) < 12) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == 1 || $key == count($bus_number_data) - 2 || $key == count($bus_number_data) - 1) {
                        if ($v['total_grade'] > 0) {
                            $data[] = $v;
                        }
                    }
                }
            }
        } elseif (count($bus_number_data) >= 3 && count($bus_number_data) < 6) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == count($bus_number_data) - 1) {
                        if ($v['total_grade'] > 0) {
                            $data[] = $v;
                        }
                    }
                }
            }
        } elseif (count($bus_number_data) <= 2) {
            foreach ($bus_number_data as $key => $bus) {
                foreach ($bus as $k => $v) {
                    if ($key == 0 || $key == count($bus_number_data) - 1) {
                        $data[] = $v;
                    }
                }
            }
        }
        if (isset($params['date'])) {
            $date = date('Y-m-01', strtotime($params['date']));
            if ($date == '1970-01-01') {
                $date = date('Y-m-01');
            }
        } else {
            $date = date('Y-m-01');
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        $curr_date = date(date('Y-m', strtotime($date)), strtotime('-1 month'));
        $up_date = date('Y-m', strtotime("$curr_date -1 month"));
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', $curr_date.'单线核算排名奖罚表')
            ->setCellValue('I2', date('t', strtotime($up_date)))
            ->setCellValue('A3', '线路')->setCellValue('B3', '车号')->setCellValue('C3', '得分')
            ->setCellValue('D3', '名次')->setCellValue('E3', '奖励/处罚')->setCellValue('F3', '应奖/惩金额')
            ->setCellValue('G3', '实际奖/惩金额')->setCellValue('H3', '司机')->setCellValue('I3', '出勤天数')
            ->setCellValue('J3', '元/天')->setCellValue('K3', '奖惩金额')
            ->setCellValue('L3', '备注')->setCellValue('M3', '车队长签字');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $num = 4;
        for ($i = 0, $iMax = count($data); $i < $iMax; $i++) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $data[$i]['line']);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $data[$i]['license_plate_num']);
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $data[$i]['total_grade']);
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $data[$i]['ranking']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $data[$i]['reward_punish_text']);
            $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $data[$i]['should_reward_punish']);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $data[$i]['real_reward_punish']);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, $data[$i]['driver']);
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num, $data[$i]['work_day']);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $data[$i]['day_money']);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $data[$i]['reward_punish_money']);
            $spreadsheet->getActiveSheet()->setCellValue('L'.$num, $data[$i]['remark']);
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num, '');
            $num++;
        }
        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:M1');

        //  设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(8.38);
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(38);
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)
            ->getAlignment()->setWrapText(true);

        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(38);

        // $spreadsheet->getActiveSheet()->getStyle('A1:A2')->applyFromArray($borderStyleArray);
        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        for ($i = 0, $iMax = count($Letter); $i < $iMax; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);

            // $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->getFont()->setSize(15);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->getFont()->setBold(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'3')->getFont()->setBold(true);
        }
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('任务工资排名');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        $spreadsheet->setActiveSheetIndex(0);
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="任务工资排名.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
